define erp='erp_mli2.';
define outils='outils_mli2.';

--CREATE TABLE se_art_compstk AS (SELECT * FROM &erp.art_compstk WHERE 1=2);
delete from se_art_compstk;

INSERT INTO se_art_compstk
(SELECT  comp_num,
         depot depot_cod,
         rang compstk_nolig,
         0 enreg_id,
         dat deb_dat,
         NULL fin_dat,
         STKMINDAT stkmini_qt,
         STKMAXDAT stkmaxi_qt
FROM (
      SELECT (SELECT &outils.stragg(comp_num) FROM &erp.art_comp c WHERE c.gene_cod||c.gene_ind=p.prodbase AND c.complet_typ='G') comp_num,
            p.prodbase,p.numspecif,codgestion,
            d.depot,To_Date('31/12/1967','dd/mm/yyyy')+dat.DATAPPSTMM dat,stkmind.STKMINDAT,stkmaxd.STKMAXDAT,
            dat.RANGMULTI3+1 rang
      FROM uv_pproduits p,UV_PPRODUITS_DEPOT d,UV_PPRODUITS_DATAPPSTMM dat,UV_PPRODUITS_STKMINDAT stkmind,UV_PPRODUITS_STKMAXDAT stkmaxd
      WHERE p.id=d.id
        AND dat.id=d.id AND dat.RANGMULTI2=d.RANGMULTI2
        AND stkmind.id(+)=dat.id AND stkmind.RANGMULTI2(+)=dat.RANGMULTI2 AND stkmind.RANGMULTI3(+)=dat.RANGMULTI3
        AND stkmaxd.id(+)=dat.id AND stkmaxd.RANGMULTI2(+)=dat.RANGMULTI2 AND stkmaxd.RANGMULTI3(+)=dat.RANGMULTI3
        AND p.codgestion IN ('1','2') /*generique sans variante et avec variante*/
) where comp_num is not null);

DELETE FROM se_art_compstk WHERE  stkmini_qt IS NULL AND stkmaxi_qt IS NULL;

UPDATE se_art_compstk tf SET enreg_id=(select enreg_id FROM &erp.art_compstk f WHERE f.comp_num=tf.comp_num AND f.depot_cod=tf.depot_cod AND f.compstk_nolig=tf.compstk_nolig) ;
UPDATE se_art_compstk tf SET enreg_id=&outils.enreg_id.NEXTVAL WHERE enreg_id IS NULL OR enreg_id =0;


CALL uvse_synchroverserp('comp_num@depot_cod@compstk_nolig','ART_COMPSTK','O') ;

commit;
